package com.zhangtai.modules.dao;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zhangtai.modules.dto.SecurityChangeDto;
import com.zhangtai.modules.dto.SecurityMemberChangeListDto;
import com.zhangtai.modules.entity.security.SecurityChangeEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * 
 *
 * @author chenshun
 * @email sunlightcs@gmail.com
 * @date 2020-08-17 20:26:04
 */
@Mapper
public interface SecurityChangeDao extends BaseMapper<SecurityChangeEntity> {


    @Select("<script>Select c.*,s.security_name,i.name from tb_security_change c " +
            "left join tb_employee_security es on es.oid = c.oid " +
            "left join tb_employee_info i on i.oid = c.oid " +
            "left join tb_security s on s.security_id = c.security_id  " +
            "<where> c.is_delete = 0 " +
            "<if test='securityId!=null'> and c.security_id = #{securityId})</if>" +
            "<if test='operationTime!=null'> and c.operation_time &lt;=#{operationTime}  </if>" +
            "<if test='type!=null'> and c.type =#{type}  </if>" +
            "<if test='name!=null'> and  i.name like '${name}'  </if>" +
            "<if test='operateName!=null'> and c.operate_name like '${operateName}'  </if>" +
            "<if test='securityType!=null'> and s.type = #{securityType}  </if>" +
            "<if test='orgIds!=null and orgIds.size>0'> and c.unit_id in " +
            " <foreach item=\"orgId\" index=\"index\" collection=\"orgIds\" open=\"(\" separator=\",\" close=\")\">#{orgId}</foreach>" +
            "</if>"+
            "<if test='orgIds==null or orgIds.size==0'> and c.unit_id = null </if>" +
            "</where> ORDER BY operation_time DESC" +
            "</script>")
    List<SecurityChangeDto> getChangeList(@Param("securityId") Long securityId, @Param("operationTime")String operationTime,
                                           @Param("type") Integer type,@Param("name") String name,@Param("operateName") String operateName,
                                           @Param("securityType") Integer securityType,
                                           @Param("orgIds") List<Long> orgIds,
                                           Page<SecurityChangeDto> page);

    @Select("select t2.type,t2.operation_time,t3.name,t3.code,t3.idcard,t3.gender,t3.hiredate," +
            "t3.beginworkdate,t3.nationality,t3.address,t3.mobile contact,t4.endowment_insurance," +
            "t4.unemployment_insurance,t4.medical_insurance,t4.injury_insurance,t4.larger_medical," +
            "t4.maternity_insurance,t4.disabled_employment,t4.reserved_funds from " +
            "(select oid,security_id,count(oid) count,max(operation_time) time from tb_security_change c  " +
            "where c.security_id = #{securityId} " +
            "and c.operation_time >= #{startTime} " +
            "and (type = 1 or type = 0)" +
            "and c.operation_time <= #{endTime}" +
            "GROUP BY oid,security_id) t1\n" +
            "left join tb_security_change t2 on t1. oid = t2.oid and t1.time = t2.operation_time\n" +
            "left join tb_employee_info t3 on t3.oid = t2.oid " +
            "left join tb_employee_security t4 on t4.oid = t3.oid and t4.security_id = t2.security_id " +
            "where t1.count%2>0 ORDER BY operation_time DESC ")
    List<SecurityMemberChangeListDto> getMemberChangeList(@Param("securityId") String securityId,
                                                          @Param("startTime") String startTime,
                                                          @Param("endTime") String endTime);


    @Select("<script>Select c.*,s.security_name,i.name from tb_security_change c " +
            "left join tb_employee_security es on es.oid = c.oid " +
            "left join tb_employee_info i on i.oid = c.oid " +
            "left join tb_security s on s.security_id = c.security_id  " +
            "<where> c.is_delete = 0 " +
            "<if test='securityId!=null'> and c.security_id = #{securityId})</if>" +
            "<if test='operationTime!=null'> and c.operation_time &lt;=#{operationTime}  </if>" +
            "<if test='type!=null'> and c.type =#{type}  </if>" +
            "<if test='name!=null'> and  i.name like '${name}'  </if>" +
            "<if test='operateName!=null'> and c.operate_name like '${operateName}'  </if>" +
            "<if test='securityType!=null'> and s.type = #{securityType}  </if>" +
            "<if test='contractIds!=null and contractIds.size>0'> and s.contract_main_id in " +
            " <foreach item=\"contractId\" index=\"index\" collection=\"contractIds\" open=\"(\" separator=\",\" close=\")\">#{contractId}</foreach>" +
            "</if>"+
            "<if test='contractIds==null or contractIds.size==0'> and s.contract_main_id = null </if>" +
            "</where> ORDER BY operation_time DESC" +
            "</script>")
    List<SecurityChangeDto> getChangeListAuth(@Param("securityId") Long securityId, @Param("operationTime")String operationTime,
                                              @Param("type") Integer type,@Param("name") String name,@Param("operateName") String operateName,
                                              @Param("securityType") Integer securityType,
                                              @Param("contractIds") List<Long> contractIds,
                                              Page<SecurityChangeDto> page);
}
